''' 滴滴打灰后端应用服务 数据库操作API （COM管理） '''
import sys,traceback,logging,datetime
import pymysql
from dbutils.pooled_db import PooledDB 
from utils.db_config import mysqlInfo
from utils.tokenutil import TokenUtil


logger = logging.getLogger('comservice')


# 创建数据库连接
def connect_db():
    pool=PooledDB(creator=pymysql,mincached=2, maxcached=5,maxshared=3, maxconnections=6, blocking=True,host=mysqlInfo['host'],port=mysqlInfo['port'],
        user=mysqlInfo['user'],password=mysqlInfo['password'],database=mysqlInfo['database'],charset=mysqlInfo['charset'])
    return pool.connection()

# 查询常见问题列表
def getChatAskList():
    conn = connect_db()
    cur = conn.cursor()

    chatlist = []
    try:
        sql = " select id AS id, reply AS reply, ask AS ask, ask_list AS askList, if_common AS ifCommon,status AS status, create_time AS createTime, update_time AS updateTime from ums_chat_preset" 
        cur.execute(sql)
        for row in cur.fetchall():
            rowdict = {}
            rowdict['id'] = row[0]
            rowdict['reply'] = row[1]
            rowdict['ask'] = row[2]
            rowdict['askList'] = row[3]
            rowdict['ifCommon'] = row[4]
            rowdict['status'] = row[5]
            rowdict['createTime'] = row[6]
            rowdict['updateTime'] = row[7]
            chatlist.append(rowdict)
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return chatlist

# 查询智能聊天记录
def getRobotLogList(param):
    conn = connect_db()
    cur = conn.cursor()

    robotlist = []
    try:
        sql = " select url.id ,url.my,url.msg,url.type,um.name as memberName,um.phone as memberPhone from ums_robot_log url left outer join ums_member um on um.member_id=url.member_id where 1 = 1 and url.member_id = %s order by id desc " 
        cur.execute(sql,(param['memberId']))
        for row in cur.fetchall():
            rowdict = {}
            rowdict['id'] = row[0]
            rowdict['my'] = row[1]
            rowdict['msg'] = row[2]
            rowdict['type'] = row[3]
            rowdict['memberName'] = row[4]
            rowdict['memberPhone'] = row[5]
            robotlist.append(rowdict)
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return robotlist

# 查询回复信息
def getChatReply(param):
    conn = connect_db()
    cur = conn.cursor()

    rowdict = {}
    rowdict['hasno'] = True
    try:
        sql = " select id AS id, reply AS reply, ask AS ask, ask_list AS askList, if_common AS ifCommon,status AS status, create_time AS createTime, update_time AS updateTime from ums_chat_preset where 1 = 1 "
        if param['ask'] is not None:
           sql += "and ask_list like CONCAT('%'," + param['ask'] + ",'%') " 
        cur.execute(sql)
        for row in cur.fetchall():
            rowdict['id'] = row[0]
            rowdict['reply'] = row[1]
            rowdict['ask'] = row[2]
            rowdict['askList'] = row[3]
            rowdict['ifCommon'] = row[4]
            rowdict['status'] = row[5]
            rowdict['createTime'] = row[6]
            rowdict['updateTime'] = row[7]
            rowdict['hasno'] = False
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return rowdict

# 新增智能聊天记录
def addRobotLog(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = "insert into ums_robot_log ( member_id, my, msg, type, create_time ) values (%s, %s, %s, %s, %s)"
        cnt = cur.execute(sql,(param['memberId'],param['my'],param['msg'],param['type'],datetime.datetime.now()))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt



